package common.sql;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import common.entity.DetailEntity;
import common.entity.DetailListEntity;
import common.entity.LeaderEntity;
import common.entity.LeaderNewsEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Objects;

import static com.han.http.util.Print.printlnErr;

/**
 * @author: Han
 * @date: 2018/8/21 9:31
 * @dscription: 数据库操作类
 */
public class Operator {

    private static final Operator OPERATOR = new Operator();

    private Operator() {
    }

    public static Operator getInstance() {
        return OPERATOR;
    }

    public void insertDetailList(String detailList, String count) {
        Connection connection;
        try {
            connection = DataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        Statement statement = null;
        String sql = "insert into detail_list_test (url, used_count, total_count) values ('" + detailList + "',0," +
                count + ");";
        try {
            statement = connection.createStatement();
            statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException e) {
            System.out.println(sql);
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(statement).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DataSource.close(connection);
        }
    }

    public List<DetailListEntity> queryDetailList() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<DetailListEntity> detailListEntityList = new ArrayList<>();
        try {
            connection = DataSource.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from detail_list_test where used_count < total_count" +
                    " and used_count != 0 order by url desc");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String url = resultSet.getString("url");
                int usedCount = resultSet.getInt("used_count");
                int totalCount = resultSet.getInt("total_count");
                String source = resultSet.getString("source");
                Timestamp lastDate = resultSet.getTimestamp("last_date");
                DetailListEntity entity = new DetailListEntity(url, source, usedCount, totalCount, lastDate);
                entity.setId(id);
                detailListEntityList.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(resultSet).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(statement).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(connection).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return detailListEntityList;
    }

    public List<LeaderEntity> queryLeaderList() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<LeaderEntity> leaderEntityList = new ArrayList<>();
        try {
            connection = DataSource.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from t_person");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                LeaderEntity entity = new LeaderEntity(id, name);
                leaderEntityList.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(resultSet).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(statement).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(connection).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return leaderEntityList;
    }

    public void updateDetailList(DetailListEntity entity) {
        Connection connection = null;
        Statement statement = null;
        String sql = "update detail_list_test set used_count = " + entity.getUsedCount() +
                ",last_date = '" + new Timestamp(entity.getLastDate().getTime()) + "' where id = " + entity.getId() + ";";
        try {
            connection = DataSource.getConnection();
            statement = connection.createStatement();
            statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException e) {
            printlnErr(sql);
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(statement).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                Objects.requireNonNull(connection).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void insertDetail(Collection<DetailEntity> entityCollection) {
        Connection connection = null;
        Statement statement;
        try {
            connection = DataSource.getConnection();
            statement = connection.createStatement();
        } catch (SQLException e) {
            try {
                Objects.requireNonNull(connection).close();
            } catch (SQLException ee) {
                ee.printStackTrace();
            }
            e.printStackTrace();
            return;
        }
        for (DetailEntity entity : entityCollection) {
            try {
                String url = entity.getUrl();
                String title = entity.getTitle();
                int isUsed = entity.getIsUsed();
                String sql = "insert into detail_test (url, title, used_flag) values " +
                        "('" + url + "','" + title + "'," + isUsed + ");";
                statement.executeUpdate(sql);
                connection.commit();
            } catch (MySQLIntegrityConstraintViolationException ignore) {
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertLeaderNews(LeaderNewsEntity entity) {
        Connection connection;
        try {
            connection = DataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        Statement statement = null;
        List<Integer> idList = entity.getIdList();
        String url = entity.getUrl();
        String time = entity.getTime();
        String title = entity.getTitle();
        String site = entity.getSite();
        String summary = entity.getSummary();
        for (int id : idList) {
            String sql = "insert into t_person_news (personId, title, url, time, site, summary, createTime, updateTime)" +
                    " values (" + id + ",'" + title + "','" + url + "','" + time + "','" + site + "','" + summary + "','"
                    + new Timestamp(System.currentTimeMillis()) + "','" + new Timestamp(System.currentTimeMillis()) + "');";
            try {
                statement = connection.createStatement();
                statement.executeUpdate(sql);
                connection.commit();
            } catch (SQLException e) {
                System.out.println(sql);
                e.printStackTrace();
            }
        }
        try {
            Objects.requireNonNull(statement).close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DataSource.close(connection);
    }
}
